-- -------------------------------------------------
--    Project Name:  订单揽收量
--    Author:        xiongv
--    date:          2023-07-07
--    depends
--          jms_dwd.dwd_yl_oms_oms_order_incre_dt 订单表
--          jms_dm.dm_outport_effect_detail_dt    全国出港时效
-- 揽收量取全国出港时效, 未揽收量取订单表, 揽收量 + 未揽收量 不一定要等于 订单量
-- -------------------------------------------------
-- 订单表 + 全国出港时效T-1
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode = 200;
set hive.exec.max.dynamic.partitions = 200;

with order_detail as (
    select
        waybill_no
        ,dt_date
        ,network_code
        ,network_name
        ,agent_code
        ,agent_name
        ,max(first_center_code)  as first_center_code
        ,max(first_center_name)  as first_center_name
        ,sum(order_flag)         as order_flag
        ,sum(taking_flag)        as taking_flag
        ,sum(not_taking_flag)    as not_taking_flag
        ,sum(collect_flag)       as collect_flag
        ,sum(not_collect_flag)   as not_collect_flag
    from(
        select
            waybill_id                          as waybill_no
            , substr(input_time, 1, 10)         as dt_date
            , pick_network_code                 as network_code
            , pick_network_name                 as network_name
            , proxy_area_code                   as agent_code
            , proxy_area_name                   as agent_name
            , null                              as first_center_code
            , null                              as first_center_name
            , 1                                 as order_flag
            , 0                                 as taking_flag
            , if(order_status_code = 103, 0, 1) as not_taking_flag
            , 0                                 as collect_flag
            , 0                                 as not_collect_flag

        from
            jms_dwd.dwd_yl_oms_oms_order_incre_dt
        where
            dt='{{ execution_date | cst_ds }}'
            and substr(input_time, 1, 10) = '{{ execution_date | cst_ds }}'
            and is_delete=1
            and not (nvl(order_source_code,'*^^*')='D02' and waybill_id is null)
            and nvl(order_status_code,-1) <> 104

        union all

        select
            waybill_no                         as waybill_no
            ,scan_date                         as dt_date
            ,sender_network_code               as network_code
            ,sender_network_name               as network_name
            ,sender_agent_code                 as agent_code
            ,sender_agent_name                 as agent_name
            ,first_center_code
            ,first_center_name
            ,0                                 as order_flag
            ,least((if_zhund  + if_liuc), 1)   as taking_flag
            ,0                                 as not_taking_flag
            ,least((if_zhund  + if_wand), 1)   as collect_flag
            ,if_weid                           as not_collect_flag
        from
            jms_dm.dm_outport_effect_detail_dt
        where
            dt = '{{ execution_date | cst_ds }}'
    ) waybill
    group by
        waybill_no
        ,dt_date
        ,network_code
        ,network_name
        ,agent_code
        ,agent_name
)
-- 交件转运中心一行改多行
, dim_network2 as (
    select  -- in_distribution_code_new 和 in_distribution_name_new 对应不上，先多关联一层
        explode_network.code
        ,explode_network.name
        ,network.code        as center_code
        ,network.name        as center_name
        ,pos
    from(
        select
            code
            ,name
            ,in_distribution_code_new
            ,in_distribution_name_new
            ,split(in_distribution_code_new, ',')[pos]       as center_code
            ,split(in_distribution_name_new, ',')[pos]       as center_name
            ,zone_code
            ,zone_name
            ,area_code
            ,area_name
            ,pos
        from
            jms_dim.dim_network_whole_massage LATERAL VIEW posexplode(split(in_distribution_code_new, ',')) t AS pos, id
        ) explode_network
            left join jms_dim.dim_network_whole_massage network
                    on explode_network.center_code = network.code
)
insert overwrite table jms_dm.dm_order_taking_delivery_summary_dt partition (dt = '{{ execution_date | cst_ds }}')
select
    dt_date
    ,null               as network_id
    ,network_code
    ,network_name
    ,agent_code  
    ,agent_name  
    ,center_code
    ,center_name
    ,zone_code
    ,zone_name
    ,area_code
    ,area_name
    ,sum(order_flag)                     as order_qty           -- 订单量
    ,sum(taking_flag)                    as taking_qty          -- 揽收量
    ,sum(not_taking_flag)                as not_taking_qty      -- 未揽收量
    ,sum(collect_flag)                   as collect_qty         -- 交件量
    ,sum(not_collect_flag)               as not_collect_qty     -- 未交件量
    ,network_sum_flag
from (
      select order_detail.dt_date
           , order_detail.network_code
           , order_detail.network_name
           , order_detail.agent_code  
           , order_detail.agent_name  
           , coalesce(order_detail.first_center_code, dim_network2.center_code) as center_code -- 始发转运中心
           , coalesce(order_detail.first_center_name, dim_network2.center_name) as center_name -- 始发转运中心
           , order_detail.order_flag                                                     -- 是否下单
           , order_detail.taking_flag                                                    -- 揽收标识
           , order_detail.not_taking_flag                                                -- 未揽收标识
           , order_detail.collect_flag                                                   -- 交件标识
           , order_detail.not_collect_flag                                               -- 未交件标识
           , dim_network1.zone_code                                                      -- 片区
           , dim_network1.zone_name                                                      -- 片区
           , dim_network1.area_code                                                      -- 底层片区
           , dim_network1.area_name                                                      -- 底层片区
           , case when dim_network2.pos >= 1 then 2 else 1 end as network_sum_flag       -- 是否可用于网点粒度汇总, 1表示可以用于网点粒度汇总, 2表示不可用于网点粒度汇总
      from order_detail
               left join
                    jms_dim.dim_network_whole_massage dim_network1
                        on order_detail.network_code = dim_network1.code
               left join
                    dim_network2 -- 出港没有到中心的运单补上中心，可能会一行变多行
                        on order_detail.network_code = dim_network2.code and order_detail.first_center_code is null
) t
group by
     dt_date
    ,network_code
    ,network_name
    ,agent_code  
    ,agent_name  
    ,center_code
    ,center_name
    ,zone_code
    ,zone_name
    ,area_code
    ,area_name
    ,network_sum_flag
distribute by 1
;